package com.github.vertical_blank.sqlformatter.Demo;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.util.JdbcConstants;
import com.alibaba.fastjson.JSON;

import java.util.List;

public class DruidSqlParserDemo {

    public static void main(String[] args) {
        // 原始SQL语句
        String sql = "SELECT * FROM users WHERE age > 18 AND city = 'Beijing'";
        sql = "select studentfileinfo.gradename,studentfileinfo.stationname,studentfileinfo.studentlayername,studentfileinfo.majorname,studentfileinfo.learningformname,class.classname,studentinfo.realname,studentfileinfo.studentno,studentinfo.cardnum,useraccount.username,studentinfo.examineenum,studentfileinfo.studentcategoryid,courseversion.versionname,semester.semestername,startedcourse.status learningstatus,totalscore.publishstatus,totalscore.examtype,totalscore.scorestatus,totalscore.calculationstatus,exambatch.batchname,examscore.examstatus,examscore.score examscore,startedcourse.studydurationstatus,startedcourse.coursewarecountstatus,examstudenttestqualification.cantaketest, examstudentbooking. status examstudentbookingstatus, totalscore.score totalscore, ifnull(examtasknum.tasknum, 0) tasknum, examscore.id examscoreid, dayscore.id dayscoreid, testscore.id testscoreid, investigationscore.id investigationscoreid, attendancescore.id attendancescoreid, belowtaskscore.id belowtaskscoreid, totalscore.id, studentfileinfo.studentid, studentfileinfo.gradeid, studentfileinfo.stationid, studentfileinfo.learningformid, studentfileinfo.majorid, studentfileinfo.studentlayerid, studentfileinfo.teachplanid, totalscore.calculationstatus calculationstatuskey, totalscore.publishstatus publishstatuskey, courseversion.id courseversionid, exambatch.id batchid, examschedule.id examscheduleid, examstudentbooking.id bookingid, online_courseversion.id onlinecourseversionid, examform.isallotexamroom, teachplan_courseversion.resulttype as scoretype, dayscore.score param4, testscore.score param64, attendancescore.score param128, investigationscore.score param256, totalscore.score, totalscore.belowtaskscore from ( select id, studentid, courseversionid, exambatchid, examscheduleid, examercourseid, examstudentbooking. status, tenantid from examstudentbooking where examstudentbooking.tenantid = ? and examstudentbooking.exambatchid =? and examstudentbooking.courseversionid in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) and examstudentbooking.examscheduleid in (?) and examstudentbooking.isdelete = 0 and examstudentbooking. status in (1, 2) ) examstudentbooking inner join studentfileinfo on studentfileinfo.studentid = examstudentbooking.studentid and studentfileinfo.isdelete = 0 and 1=1 and studentfileinfo.tenantid = examstudentbooking.tenantid and studentfileinfo.stationid in (?,?,?,?,?) and studentfileinfo.gradeid in (?) and studentfileinfo.majorid in (?,?,?,?,?,?,?,?,?,?,?,?,?) and studentfileinfo.learningformid in (?,?,?,?,?) and studentfileinfo.studentlayerid in (?,?,?,?,?,?) inner join studentinfo on studentinfo.id = examstudentbooking.studentid and studentinfo.isdelete = 0 inner join useraccount on useraccount.id = studentinfo.useraccountid and useraccount.isdelete = 0 inner join startedcourse on startedcourse.studentid = examstudentbooking.studentid and startedcourse.courseversionid = examstudentbooking.courseversionid and startedcourse.datastatus = 1 and startedcourse.isdelete = 0 and startedcourse.status in (0,1,2) inner join courseversion on examstudentbooking.courseversionid = courseversion.id and courseversion.isdelete = 0 inner join teachplan_courseversion on teachplan_courseversion.courserversionid = examstudentbooking.courseversionid and teachplan_courseversion.isdelete = 0 and studentfileinfo.teachplanid = teachplan_courseversion.teachplanid left join student_class on studentfileinfo.studentid = student_class.studentid left join class on student_class.classid = class.id and class.isdelete = 0 left join exambatch on examstudentbooking.exambatchid = exambatch.id and exambatch.isdelete = 0 left join examercourse on examercourse.id = examstudentbooking.examercourseid and examercourse.isdelete = 0 left join examform on examercourse.examformid = examform.id and examercourse.isdelete = 0 left join online_courseversion on online_courseversion.courseversionid = examstudentbooking.courseversionid and online_courseversion.isdelete = 0 and online_courseversion.exambatchid = examstudentbooking.exambatchid left join totalscore on totalscore.examstudentbookingid = examstudentbooking.id and totalscore.isdelete = 0 and totalscore.exambatchid = examstudentbooking.exambatchid left join examscore on examscore.examstudentbookingid = examstudentbooking.id and examscore.isdelete = 0 left join dayscore on dayscore.studentid = examstudentbooking.studentid and dayscore.courseversionid = examstudentbooking.courseversionid and dayscore.tenantid = examstudentbooking.tenantid and dayscore.isdelete = 0 left join testscore on testscore.studentid = examstudentbooking.studentid and testscore.courseversionid = examstudentbooking.courseversionid and testscore.tenantid = examstudentbooking.tenantid and testscore.isdelete = 0 left join investigationscore on investigationscore.studentid = examstudentbooking.studentid and investigationscore.courseversionid = examstudentbooking.courseversionid and investigationscore.tenantid = examstudentbooking.tenantid and investigationscore.isdelete = 0 left join attendancescore on attendancescore.studentid = examstudentbooking.studentid and attendancescore.courseversionid = examstudentbooking.courseversionid and attendancescore.tenantid = attendancescore.tenantid and attendancescore.isdelete = 0 left join belowtaskscore on examstudentbooking.studentid = belowtaskscore.studentid and courseversion.id = belowtaskscore.courseversionid and belowtaskscore.isdelete = 0 left join examtasknum on examstudentbooking.courseversionid = examtasknum.courseversionid and examtasknum.isdelete = 0 and examtasknum.tenantid = ? and examtasknum.orgid = ? left join examschedule on examstudentbooking.examscheduleid = examschedule.id and examschedule.isdelete = 0 and examstudentbooking.exambatchid = examschedule.exambatchid left join semester on semester.id = teachplan_courseversion.semesterid and semester.isdelete = 0 left join examstudenttestqualification on examstudenttestqualification.exambatchid = examstudentbooking.exambatchid and examstudenttestqualification.courseversionid = examstudentbooking.courseversionid and examstudenttestqualification.studentid = examstudentbooking.studentid and examstudenttestqualification.isdelete = 0 where (totalscore.publishstatus=0 or totalscore.publishstatus is null) and totalscore.calculationstatus is null order by studentfileinfo.studentno asc, convert(courseversion.versionname using gbk) asc limit 0,50;";
        sql = "SELECT c.customer_name AS customer, o.order_id AS order_id, oi.product_id AS product_id, p.product_name AS product_name, p.avg_price AS average_price, s.supplier_name AS supplier, CASE WHEN p.avg_price > 50 THEN 'Expensive' ELSE 'Affordable' END AS price_category FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.order_date BETWEEN '2024-01-01' AND '2024-08-19' LEFT JOIN order_items oi ON o.order_id = oi.order_id AND oi.quantity > 0 LEFT JOIN ( SELECT product_id, AVG(price) AS avg_price FROM products GROUP BY product_id ) p ON oi.product_id = p.product_id LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id WHERE c.customer_id IN (1001, 1002);";
        //sql = "SELECT c.customer_name AS customer, o.order_id AS order_id, oi.product_id AS product_id, p.product_name AS product_name, p.avg_price AS average_price, s.supplier_name AS supplier, CASE WHEN p.avg_price > 50 THEN 'Expensive' ELSE 'Affordable' END AS price_category FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.order_date BETWEEN '2024-01-01' AND '2024-08-19' LEFT JOIN order_items oi ON o.order_id = oi.order_id AND oi.quantity > 0 LEFT JOIN ( SELECT product_id, AVG(price) AS avg_price FROM products GROUP BY product_id ) p ON oi.product_id = p.product_id LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id WHERE c.customer_id IN (1001, 1002);";
        // 格式化SQL语句
        String formattedSql = SQLUtils.format(sql, JdbcConstants.MYSQL);
        //System.out.println("Formatted SQL: \n" + formattedSql);

        // 解析SQL语句
        List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);

        System.out.println(JSON.toJSONString(stmtList));
        // 遍历解析后的SQL语句列表，进行自定义规则检查
        for (SQLStatement stmt : stmtList) {

            //System.out.println(stmt);
//            // 在这里添加自定义规则检查逻辑，例如检查表名、字段名等
//            if (stmt instanceof MySQLSelectStatement) {
//                MySQLSelectStatement selectStmt = (MySQLSelectStatement) stmt;
//                // 获取查询的表名
//                TableName tableName = selectStmt.getFrom().getAlias();
//                System.out.println("Table name: " + tableName.getName());
//            }
        }
    }
}

